﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Tools.Ribbon;
using System.IO;
using System.Windows.Forms;
using System.Data;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using DevScope.Samples.Excel.Toolkit;

namespace DevScope.Samples.DataInsights.ExcelAddin
{
    public partial class MyRibbon
    {
        private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
        {
            try
            {
                RefreshTemplates();
            }

            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void RefreshTemplates()
        {
            try
            {
                var templateList = Directory.GetFiles(GetTemplatesFolder(), "*.xlsx");

                //Order by last date modified
                var list = templateList.AsEnumerable().Select(s =>
                    {
                        return new
                                         {
                                             Filename = s,
                                             LastMod = File.GetLastWriteTime(s)
                                         };
                    }).OrderByDescending(s => s.LastMod).ToList();


                dropDownTemplates.Items.Clear();

                list.ForEach(file => 
                {
                    RibbonDropDownItem item = Globals.Factory.GetRibbonFactory().CreateRibbonDropDownItem();

                    item.Label = Path.GetFileNameWithoutExtension(file.Filename);

                    dropDownTemplates.Items.Add(item);

                });

            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private string GetTemplatesFolder()
        {
            return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Templates");
        }

        private string GetSamplesFolder()
        {
            return Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Samples");
        }

        private void btnExplore_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                var app = Globals.ThisAddIn.Application;


                if (app.ActiveCell.ListObject == null)
                    throw new ApplicationException("Please select a table");

                if (dropDownTemplates.SelectedItem == null)
                    throw new ApplicationException("Please select a template");

                var sourceLO = app.ActiveCell.ListObject;

                ExploreList(app, sourceLO);


            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void ExploreList(Microsoft.Office.Interop.Excel.Application app, ListObject sourceLO)
        {
            var started = DateTime.Now;

            var analyticModuleName = sourceLO.Name;

            var dashboardFileTemplate = Path.Combine(GetTemplatesFolder(), dropDownTemplates.SelectedItem.Label + ".xlsx");

            if (!File.Exists(dashboardFileTemplate))
                throw new ApplicationException("Template not found:" + dashboardFileTemplate);

            var dashboardFile = Path.GetDirectoryName(app.ActiveWorkbook.FullName) + @"\" +
                        analyticModuleName + "_Insights_" + Guid.NewGuid().ToString() + ".xlsx";

            UpdateStatus("Preparing template...");

            if (File.Exists(dashboardFile))
                throw new ApplicationException("File already exists:  " + dashboardFile);

            File.Copy(dashboardFileTemplate, dashboardFile);

            UpdateStatus("Opening template...");
            var dashboardBook = app.Workbooks.Open(dashboardFile);

            Worksheet defaultSheet = (Worksheet)dashboardBook.ActiveSheet;
            
            UpdateStatus("Copying data...");

            app.ScreenUpdating = false;

            Worksheet sheet = (Worksheet)dashboardBook.Sheets.Add();
            sheet.Name = analyticModuleName;
            sourceLO.Range.Copy();

            sheet.Paste();

            UpdateStatus("Adding rowid...");
            var destListLO = sheet.ListObjects[1];
            destListLO.Name = analyticModuleName;
            destListLO.ListColumns.Add(1);
            destListLO.ListColumns[1].Name = "RowId";
            destListLO.ListColumns[1].Range.NumberFormat = "General";

            Range cell = (Range)destListLO.ListColumns[1].DataBodyRange.Cells[1, 1];
            cell.Value2 = 1;
            Range cell2 = (Range)destListLO.ListColumns[1].DataBodyRange.Cells[2, 1];
            cell2.Value2 = 2;
            Range two = app.Union(cell, cell2);
            two.Select();
            two.AutoFill(destListLO.ListColumns[1].DataBodyRange);

            sheet.Visible = XlSheetVisibility.xlSheetHidden;

            app.ScreenUpdating = true;

            UpdateStatus("Adding table to Data Model...");

            dashboardBook.Connections.Add2(
                "DevScope.Samples.Excel.DataInsights." + sheet.Name + "!" + destListLO.Name,
                "DevScope.Samples.Excel.DataInsights." + sheet.Name + "!" + destListLO.Name,
                "WORKSHEET;" + sheet.Name,
                sheet.Name + "!" + destListLO.Name,
                7, true, false);
            
            var destTableData = destListLO.ToDataTable();

            var dateCol = "";
            var instanceCol = "";
            var pictureCol = "";
            var locationCol = "";


            UpdateStatus("Analyzing schema...");
            List<string> metrics = new List<string>();
            List<string> textColumns = new List<string>();

            foreach (DataColumn tableCol in destTableData.Columns)
            {
                if (tableCol.ColumnName == "RowId")
                    continue;
                object value = destTableData.Rows[0][tableCol];

                if (value == null)
                    throw new ApplicationException("Null value not supported at row 1, column " + tableCol.ColumnName);

                if (value.GetType() == typeof(DateTime) && dateCol == "")
                {
                    dateCol = tableCol.ColumnName;
                    continue;
                }

                if (value.GetType() == typeof(Int32) ||
                    value.GetType() == typeof(double) ||
                    value.GetType() == typeof(Int64) ||
                    value.GetType() == typeof(Int64)
                    )
                {
                    metrics.Add(tableCol.ColumnName);
                    continue;
                }

                if (value is string && instanceCol == "")
                    instanceCol = tableCol.ColumnName;

                if (value is string)
                {
                    var s = (value + "").ToLower();

                    if ((
                        s.StartsWith("http://") ||
                        s.EndsWith(".png") || s.EndsWith(".jpg") ||
                        s.EndsWith(".gif") || s.EndsWith(".jpeg"))
                        && pictureCol == "")
                    {
                        pictureCol = tableCol.ColumnName;
                    }
                    else if (tableCol.ColumnName.ToLower() == "country"
                        || tableCol.ColumnName.ToLower() == "location"
                        || tableCol.ColumnName.ToLower() == "city"
                        || tableCol.ColumnName.ToLower() == "address")
                    {
                        locationCol = tableCol.ColumnName;
                    }
                    else
                        textColumns.Add(tableCol.ColumnName);

                }
            }

            Dictionary<string, int> colCount = new Dictionary<string, int>();

            foreach (var item in textColumns)
            {

                var count = destTableData.AsEnumerable().Select(s =>
                {
                    return new
                    {
                        ColValue = s[item],
                    };
                }).Distinct().Count();

                colCount[item] = count;
            }
            //categories are less than rowcount/2
            var categoryCol = "" + textColumns.AsEnumerable().Where(s => colCount[s] < destTableData.Rows.Count / 2).FirstOrDefault();

            //detailCol has same row count
            var detailCol = "" + textColumns.AsEnumerable().Where(s => colCount[s] == destTableData.Rows.Count).FirstOrDefault();

            if (string.IsNullOrEmpty(dateCol))
                throw new ApplicationException("Date column not found.");

            UpdateStatus("Transforming metrics...");

            var row = 1;

            var factList = destTableData.AsEnumerable().Select(s =>
            {
                return new
                {
                    FactKey = row++,
                    Factid = Convert.ToInt32(s["Rowid"]),
                    MetricKey = 1,
                    DateKey = 0,
                    Timekey = Convert.ToInt32(((DateTime)s[dateCol]).ToString("HHmm")),
                    LocationKey = 0,
                    FactValue = s[metrics[0]],
                    Date = ((DateTime)s[dateCol]).Date,
                    Instance = instanceCol != "" ? s[instanceCol] : null,
                    Picture = pictureCol != "" ? s[pictureCol] : null,
                    Category = categoryCol != "" ? s[categoryCol] : "All",
                    Detail = detailCol != "" ? s[detailCol] : null,
                    Location = locationCol != "" ? s[locationCol] : null
                };
            }
            ).ToList();

            for (int i = 1; i < metrics.Count; i++)
            {
                UpdateStatus("Transforming metrics ({0})...", row);

                var factListT = destTableData.AsEnumerable().Select(s =>
                {
                    return new
                    {
                        FactKey = row++,
                        Factid = Convert.ToInt32(s["Rowid"]),
                        MetricKey = i + 1,
                        DateKey = 0,
                        Timekey = Convert.ToInt32(((DateTime)s[dateCol]).ToString("HHmm")),
                        LocationKey = 0,
                        FactValue = s[metrics[i]],
                        Date = ((DateTime)s[dateCol]).Date,
                        Instance = instanceCol != "" ? s[instanceCol] : null,
                        Picture = pictureCol != "" ? s[pictureCol] : null,
                        Category = categoryCol != "" ? s[categoryCol] : "All",
                        Detail = detailCol != "" ? s[detailCol] : null,
                        Location = locationCol != "" ? s[locationCol] : null
                    };
                }
            ).ToList();

                factList.InsertRange(0, factListT);
            }

            UpdateStatus("Loading Values (rows:{0})...", factList.Count);

            app.EnableEvents = false;
            app.Calculation = XlCalculation.xlCalculationManual;

            var factListObject = ((Worksheet)dashboardBook.Worksheets["Facts"]).GetRange("Values");

            factListObject.Fill(factList, OrientationEnum.Rows);
            if (dateCol != "")
                destListLO.ListColumns[dateCol].Range.NumberFormat = "yyyy-m-d";

            app.EnableEvents = true;
            app.Calculation = XlCalculation.xlCalculationAutomatic;


            UpdateStatus("Loading metrics list ({0})...", metrics.Count);


            row = 1;
            var listMetrics = metrics.AsEnumerable().Select(s =>
            {
                return new
                {
                    MetricId = row++,
                    Metric = s,
                    Aggregation = s.ToLower().StartsWith("avg") ? 2 : 1
                };
            }).ToList();

            UpdateStatus("Adding model relationships...");

            var metricsLO = ((Worksheet)dashboardBook.Worksheets["Metrics"]).GetRange("Metrics");
            metricsLO.Fill(listMetrics, OrientationEnum.Rows);

            var colFactId = dashboardBook.Model.ModelTables["Values"].ModelTableColumns["FactId"];

            var colRowId = dashboardBook.Model.ModelTables[destListLO.Name].ModelTableColumns["RowId"];

            dashboardBook.Model.ModelRelationships.Add(colFactId, colRowId);

            var globalsLO = ((Worksheet)dashboardBook.Worksheets["Global"]).GetRange("Global");

            UpdateStatus("Updating status...");

            Range status = (Range)((Range)globalsLO.Rows[1]).Columns[2];
            status.Value2 = "Data insights finished: " + analyticModuleName;

            UpdateStatus("Refreshing data...");
            dashboardBook.Model.Refresh();
            dashboardBook.RefreshAll();

            defaultSheet.Activate();

            var elapsed = DateTime.Now.Subtract(started);

            UpdateStatus("Done! (time: {0:0.00} secs)", elapsed.TotalSeconds);
        }

        private void UpdateStatus(string status, params object[] args)
        {
            Globals.ThisAddIn.Application.StatusBar = "Data Insights: " + string.Format(status, args);
        }

        private static void HandleError(Exception ex)
        {
            if (ex is ApplicationException)
                MessageBox.Show(ex.Message);
            else
                MessageBox.Show(ex.ToString());
        }

        private void btnRefresh_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                RefreshTemplates();
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void btnBrowse_Click(object sender, RibbonControlEventArgs e)
        {
            ProcessStartInfo startUrlInfo = new ProcessStartInfo();

            startUrlInfo.FileName = GetTemplatesFolder();

            Process.Start(startUrlInfo);
        }

        private void dropDownTemplates_SelectionChanged(object sender, RibbonControlEventArgs e)
        {

        }

        private void btnExploreAll_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                var app = Globals.ThisAddIn.Application;

                if (dropDownTemplates.SelectedItem == null)
                    throw new ApplicationException("Please select a template");

                foreach (Worksheet sheet in app.ActiveWorkbook.Sheets)
                {
                    if (sheet.Visible != XlSheetVisibility.xlSheetVisible)
                        continue;

                    foreach (ListObject sourceLO in sheet.ListObjects)
                    {
                        ExploreList(app, sourceLO);
                    }
                }



            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void btnAdd_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                throw new ApplicationException("Not implemented");
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void button1_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                var sampleFIle = Path.Combine(GetSamplesFolder(), "DataInsights_Sample.xlsx");
                Globals.ThisAddIn.Application.Workbooks.Open(sampleFIle);
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void btnUpdateFromTemplate_Click(object sender, RibbonControlEventArgs e)
        {
            try
            {
                throw new ApplicationException("Not implemented");
            }
            catch (Exception ex)
            {
                HandleError(ex);
            }
        }

        private void btnAbout_Click(object sender, RibbonControlEventArgs e)
        {
            MessageBox.Show("By DevScope BI Team\r\n@ SqlSat #188 Portugal\r\n\r\nVisit us at:\r\nwww.devscope.net\r\nrquintino.wordpress.com");
        }

        private void btnSqlSat_Click(object sender, RibbonControlEventArgs e)
        {
            ProcessStartInfo startUrlInfo = new ProcessStartInfo();

            Process.Start("http://www.sqlsaturday.com/188/eventhome.aspx");
        }

        private void btnSamples_Click(object sender, RibbonControlEventArgs e)
        {
             ProcessStartInfo startUrlInfo = new ProcessStartInfo();

             Process.Start("http://devscopebisamples.codeplex.com");
            
        }
    }
}
